package db.sqldao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import db.DBManager;
import db.domain.SystemPictur;

public class SystemPicturSqlDao {

	public boolean addSytemPictur(SystemPictur SystemPictur) {
		// 定义数据库连接对象
		Connection conn = null;
		// 定义执行SQL语句的装载器
		PreparedStatement stm = null;
		// 定义字符串
		String sql = "insert into t_SytemPictur(no,pictur1,pictur2,pictur3,pictur4,pictur5,pictur6,pictur7,pictur8,pictur9,pictur10,name) values (?,?,?,?,?,?,?,?,?,?,?)";
		try {
			// （1）创建数据库连接
			conn = DBManager.getConnection();
			// （2）将SQL语句添加到装载器中
			stm = conn.prepareStatement(sql);
			// （3）给参数进行赋值
			stm.setInt(1, SystemPictur.getNo());// 姓名
			stm.setString(2, SystemPictur.getPictur1());
			stm.setString(3, SystemPictur.getPictur2());
			stm.setString(4, SystemPictur.getPictur3());
			stm.setString(5, SystemPictur.getPictur4());
			stm.setString(6, SystemPictur.getPictur5());
			stm.setString(7, SystemPictur.getPictur6());
			stm.setString(8, SystemPictur.getPictur7());
			stm.setString(9, SystemPictur.getPictur8());
			stm.setString(10, SystemPictur.getPictur9());
			stm.setString(11, SystemPictur.getPictur10());
			stm.setString(12, SystemPictur.getName());
			// （4）执行SQL
			stm.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			// （5）关闭数据库的连接
			try {
				stm.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {

	}

	/**
	 * 根据主键，修改用户信息
	 * 
	 * @param SystemPictur用户对象
	 * @return修改成功返回true，否则返回false
	 */
	public boolean updateSystemPictur(SystemPictur SystemPictur) {
		// 定义数据库连接对象
		Connection conn = null;
		// 定义执行SQL语句的装载器
		PreparedStatement stm = null;
		// 定义字符串
		String sql = "update t_SystemPictur set pictur1=?,pictur2=?,pictur3=?,pictur4=?,pictur5=?,pictur6=?,pictur7=?,pictur8=?,pictur9=?,pictur10=?,name=? where no=?";
		try {
			// （1）创建数据库连接
			conn = DBManager.getConnection();
			// （2）将SQL语句添加到装载器中
			stm = conn.prepareStatement(sql);
			// （3）给参数进行赋值
			stm.setInt(12, SystemPictur.getNo());
			stm.setString(1, SystemPictur.getPictur1());
			stm.setString(2, SystemPictur.getPictur2());
			stm.setString(3, SystemPictur.getPictur3());
			stm.setString(4, SystemPictur.getPictur4());
			stm.setString(5, SystemPictur.getPictur5());
			stm.setString(6, SystemPictur.getPictur6());
			stm.setString(7, SystemPictur.getPictur7());
			stm.setString(8, SystemPictur.getPictur8());
			stm.setString(9, SystemPictur.getPictur9());
			stm.setString(10, SystemPictur.getPictur10());
			stm.setString(11, SystemPictur.getName());
			// （4）执行SQL
			stm.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			// （5）关闭数据库的连接
			try {
				stm.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 根据主键，删除；用户信息
	 * 
	 * @param no学号
	 * @return删除成功返回true，否则返回false
	 */
	public boolean deleteSystemPictur(int no) {
		// 定义数据库连接对象
		Connection conn = null;
		// 定义执行SQL语句的装载器
		PreparedStatement stm = null;
		// 定义字符串
		String sql = "delete from t_SystemPictur where no=?";
		try {
			// （1）创建数据库连接
			conn = DBManager.getConnection();
			// （2）将SQL语句添加到装载器中
			stm = conn.prepareStatement(sql);
			// （3）给参数进行赋值
			stm.setInt(1, no);// 学号
			// （4）执行SQL
			stm.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			// （5）关闭数据库的连接
			try {
				stm.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 根据主键，查询用户信息
	 * 
	 * @param no编号
	 * @return返回对象，如果没有数据返回null
	 */
	public SystemPictur getSystemPictur(int no) {
		// 定义数据库连接对象
		Connection conn = null;
		// 定义执行SQL语句的装载器
		PreparedStatement stm = null;
		// 定义字符串
		String sql = "select * from t_SystemPictur where no=?";
		// 定义结果集
		ResultSet rs = null;
		// 定义用户对象
		SystemPictur SystemPictur = null;
		try {
			// （1）创建数据库连接
			conn = DBManager.getConnection();
			// （2）将SQL语句添加到装载器中
			stm = conn.prepareStatement(sql);
			// （3）给参数进行赋值
			stm.setInt(1, no);
			// （4）执行SQL
			rs = stm.executeQuery();
			while (rs.next()) {
				SystemPictur = new SystemPictur();
				SystemPictur.setNo(rs.getInt("no"));
				SystemPictur.setPictur1(rs.getString("pictur1"));
				SystemPictur.setPictur2(rs.getString("pictur2"));
				SystemPictur.setPictur3(rs.getString("pictur3"));
				SystemPictur.setPictur4(rs.getString("pictur4"));
				SystemPictur.setPictur5(rs.getString("pictur5"));
				SystemPictur.setPictur6(rs.getString("pictur6"));
				SystemPictur.setPictur7(rs.getString("pictur7"));
				SystemPictur.setPictur8(rs.getString("pictur8"));
				SystemPictur.setPictur9(rs.getString("pictur9"));
				SystemPictur.setPictur10(rs.getString("pictur10"));
				SystemPictur.setName(rs.getString("name"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// （5）关闭数据库的连接
			try {
				rs.close();
				stm.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return SystemPictur;
	}

	/**
	 * 根据指定查询条件，查询用户信息
	 * 
	 * @param whereSQL查询条件
	 *            ，例如：where sex='男'
	 * @return
	 */
	public ArrayList<SystemPictur> getSystemPictur(String whereSQL) {
		// 定义数据库连接对象
		Connection conn = null;
		// 定义执行SQL语句的装载器
		PreparedStatement stm = null;
		// 定义字符串
		String sql = "select * from t_SystemPictur " + whereSQL;
		// 定义结果集
		ResultSet rs = null;
		// 定义用户对象集合
		ArrayList<SystemPictur> SystemPicturs = new ArrayList<SystemPictur>();
		try {
			// （1）创建数据库连接
			conn = DBManager.getConnection();
			// （2）将SQL语句添加到装载器中
			stm = conn.prepareStatement(sql);
			// （3）给参数进行赋值

			// （4）执行SQL
			rs = stm.executeQuery();
			while (rs.next()) {
				SystemPictur SystemPictur = new SystemPictur();
				SystemPictur.setNo(rs.getInt("no"));
				SystemPictur.setPictur1(rs.getString("pictur1"));
				SystemPictur.setPictur2(rs.getString("pictur2"));
				SystemPictur.setPictur3(rs.getString("pictur3"));
				SystemPictur.setPictur4(rs.getString("pictur4"));
				SystemPictur.setPictur5(rs.getString("pictur5"));
				SystemPictur.setPictur6(rs.getString("pictur6"));
				SystemPictur.setPictur7(rs.getString("pictur7"));
				SystemPictur.setPictur8(rs.getString("pictur8"));
				SystemPictur.setPictur9(rs.getString("pictur9"));
				SystemPictur.setPictur10(rs.getString("pictur10"));
				SystemPictur.setName(rs.getString("name"));
				SystemPicturs.add(SystemPictur);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// （5）关闭数据库的连接
			try {
				rs.close();
				stm.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return SystemPicturs;
	}

	/**
	 * 根据指定的查询条件，查询用户信息
	 * 
	 * @param whereSQL查询条件
	 * @return
	 */
	public ArrayList<SystemPictur> getSystemPictur(String whereSQL, int start,
			int end) {
		// 定义数据库连接对象
		Connection conn = null;
		// 定义执行SQL语句的装载器
		PreparedStatement stm = null;
		// 定义字符串
		String sql = "select * from t_SystemPictur " + whereSQL;
		// 定义结果集
		ResultSet rs = null;
		// 定义用户对象集合
		ArrayList<SystemPictur> SystemPicturs = new ArrayList<SystemPictur>();
		try {
			// （1）创建数据库连接
			conn = DBManager.getConnection();
			// （2）将SQL语句添加到装载器中
			stm = conn.prepareStatement(sql);
			// （3）给参数进行赋值
			// （4）执行SQL
			rs = stm.executeQuery();
			if (start > 1) {
				rs.absolute(start - 1);// 改变游标的位置
			}
			for (int i = start; i <= end; i++) {
				if (rs.next()) {
					SystemPictur SystemPictur = new SystemPictur();
					SystemPictur.setNo(rs.getInt("no"));
					SystemPictur.setPictur1(rs.getString("pictur1"));
					SystemPictur.setPictur2(rs.getString("pictur2"));
					SystemPictur.setPictur3(rs.getString("pictur3"));
					SystemPictur.setPictur4(rs.getString("pictur4"));
					SystemPictur.setPictur5(rs.getString("pictur5"));
					SystemPictur.setPictur6(rs.getString("pictur6"));
					SystemPictur.setPictur7(rs.getString("pictur7"));
					SystemPictur.setPictur8(rs.getString("pictur8"));
					SystemPictur.setPictur9(rs.getString("pictur9"));
					SystemPictur.setPictur10(rs.getString("pictur10"));
					SystemPictur.setName(rs.getString("name"));
					SystemPicturs.add(SystemPictur);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// （5）关闭数据库的连接
			try {
				rs.close();
				stm.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return SystemPicturs;
	}

	/**
	 * 根据查询条件，返回对应记录的总条数
	 * 
	 * @param whereSql查询条件
	 * @return
	 */
	public int getAllSystemPicturs(String whereSql) {
		// 定义数据库连接对象
		Connection conn = null;
		// 定义执行SQL语句的装载器
		PreparedStatement stm = null;
		// 定义字符串
		String sql = "select count(*) as count from t_SystemPictur " + whereSql;
		// 定义结果集
		ResultSet rs = null;
		int count = 0;
		try {
			// （1）创建数据库连接
			conn = DBManager.getConnection();
			// （2）将SQL语句添加到装载器中
			stm = conn.prepareStatement(sql);
			// （4）执行SQL
			rs = stm.executeQuery();
			while (rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// （5）关闭数据库的连接
			try {
				rs.close();
				stm.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count;
	}

}
